# -*- coding: utf-8 -*-
'''
Created on 11-10-2012

@author: LONG HOANG GIANG
'''
import os, sys
sys.path.append(os.path.join(os.path.dirname(__file__), '../'))
from CrawlerLib.sqlitehelper import SQLiteHelper


TABLE_CHAPTER = "udv_chapters"
TABLE_DETAIL = "udv_details"


class StoryModel():
    
    _db = None
    _database = ''
    
    def __init__(self, database):
        self._database = database
    
    def open(self, **kwargs):
        if kwargs.get('drop', False):
            self.dropDatabase(self._database)
        self._db = SQLiteHelper(self._database)
        self.drop_tables()
        self.create_tables()
        
    def convert_database(self):
        pass
    
    def create_tables(self):
        self._db.execute("CREATE TABLE IF NOT EXISTS {0}(`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT);".format(TABLE_CHAPTER))
        self._db.execute("CREATE INDEX IF NOT EXISTS chapter_idx ON {0}(id)".format(TABLE_CHAPTER))
        self._db.execute("CREATE TABLE IF NOT EXISTS {0}(`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `chapterId` INTEGER, `detail` TEXT)".format(TABLE_DETAIL))
        self._db.execute("CREATE INDEX IF NOT EXISTS detail_idx ON {0}(chapterId)".format(TABLE_DETAIL))
        self._db.commit()

    def drop_tables(self):
        sql = '''DROP TABLE IF EXISTS {tblchapter};
                 DROP TABLE IF EXISTS {tbldetail};
        '''.format(tblchapter=TABLE_CHAPTER, tbldetail=TABLE_DETAIL)
        self._db.executescript(sql)
        self._db.commit()

    def addChapter(self, chapterName):
        cid = self._db.execute("INSERT INTO {0}(title) VALUES(?)".format(TABLE_CHAPTER), chapterName)
        self._db.commit()
        return cid
        
    def addDetail(self, cid, detail):
        self._db.execute("INSERT INTO {0}(chapterId, detail) VALUES(?, ?)".format(TABLE_DETAIL), [cid, detail])
        self._db.commit()
    
    def getAllChapters(self):
        self._db.execute("SELECT * FROM {0}".format(TABLE_CHAPTER))
        rows = self._db.fetchall()
        return rows
    
    def getAllDetails(self):
        self._db.execute("SELECT * FROM {0}".format(TABLE_DETAIL))
        rows = self._db.fetchall()
        return rows
    
    def getDetailByChapterId(self, cid):
        self._db.execute("SELECT * FROM {0} WHERE chapterId=?".format(TABLE_DETAIL), cid)
        row = self._db.fetchone()
        return row
    
    def deleteChapter(self, id):
        self._db.execute("DELETE FROM {0} WHERE id=?".format(TABLE_CHAPTER), id)
        self._db.execute("DELTE FROM {0} WHERE chapterId=?".format(TABLE_DETAIL), id)
        self._db.commit()
    
    def updateChapter(self, id, chapterName):
        self._db.execute("UPDATE {0} SET title=? WHERE id=?".format(TABLE_CHAPTER), (chapterName, id))
        self._db.commit()
        
    def updateDetail(self, id, detail):
        self._db.execute("UPDATE {0} SET detail=? WHERE id=?".format(TABLE_DETAIL), (detail, id))
        self._db.commit()
        
    def dropDatabase(self):
        if self._db != None: 
            self._db.close()
        if os.path.isfile(self._database):
            os.unlink(self._database)
    
    def close(self):
        self._db.close()
